package com.orchard.orchardfarmmanagementsystem.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.orchard.orchardfarmmanagementsystem.entity.Task;
import com.orchard.orchardfarmmanagementsystem.entity.User;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface TaskMapper extends BaseMapper<Task> {

    @Insert("INSERT INTO agriculturaltask (task_name, description, created_at, due_date, status, priority) VALUES (#{taskName}, #{description}, #{createdAt}, #{dueDate}, #{status}, #{priority})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertTask(Task task);

    @Select("<script>" +
            "SELECT " +
            "t.id, " +
            "t.task_name as name, " +
            "t.description, " +
            "t.created_at, " +
            "t.due_date as end_date, " +
            "t.status, " +
            "u.username as assignee, " +
            "(SELECT GROUP_CONCAT(DISTINCT o.name SEPARATOR ', ') " +
            " FROM taskorchardrelation tor " +
            " LEFT JOIN orchard_info o ON tor.orchard_id = o.id " +
            " WHERE tor.task_id = t.id) as orchard_info, " +
            "(SELECT GROUP_CONCAT(DISTINCT CONCAT(m.name, 'x', tmr.required_quantity) SEPARATOR ', ') " +
            " FROM taskmaterialrelation tmr " +
            " LEFT JOIN materials m ON tmr.material_id = m.id " +
            " WHERE tmr.task_id = t.id) as materials_tools " +
            "FROM agriculturaltask t " +
            "LEFT JOIN taskassignment ta ON t.id = ta.task_id " +
            "LEFT JOIN user u ON ta.user_id = u.user_id " +
            "<where>" +
            "<if test='queryParams != null'>" +
            "<if test='queryParams.name != null and queryParams.name.trim() != \"\"'> AND t.task_name LIKE CONCAT('%', #{queryParams.name}, '%')</if>" +
            "<if test='queryParams.assignee != null and queryParams.assignee.trim() != \"\"'> AND u.username LIKE CONCAT('%', #{queryParams.assignee}, '%')</if>" +
            "<if test='queryParams.orchardInfo != null and queryParams.orchardInfo.trim() != \"\"'> AND EXISTS (" +
            "   SELECT 1 FROM taskorchardrelation tor LEFT JOIN orchard_info o ON tor.orchard_id = o.id " +
            "   WHERE tor.task_id = t.id AND o.name LIKE CONCAT('%', #{queryParams.orchardInfo}, '%')" +
            ") </if>" +
            "<if test='queryParams.createdAt != null and queryParams.createdAt.trim() != \"\"'> AND DATE(t.created_at) = DATE(#{queryParams.createdAt})</if>" +
            "<if test='queryParams.status != null and queryParams.status.trim() != \"\"'> AND t.status = #{queryParams.status}</if>" +
            "</if>" +
            "</where> " +
            "GROUP BY " +
            "t.id, t.task_name, t.description, t.created_at, t.due_date, t.status, u.username " +
            "</script>")
    List<Map<String, Object>> selectTasksWithRelations(@Param("queryParams") Map<String, String> queryParams);

//    @Select("<script>" +
//            "SELECT " +
//            "t.id, " +
//            "t.task_name as name, " +
//            "t.description, " +
//            "t.created_at, " +
//            "t.due_date as end_date, " +
//            "t.status, " +
//            "u.username as assignee, " +
//            "(SELECT GROUP_CONCAT(DISTINCT o.name SEPARATOR ', ') " +
//            " FROM taskorchardrelation tor " +
//            " LEFT JOIN orchard_info o ON tor.orchard_id = o.id " +
//            " WHERE tor.task_id = t.id) as orchard_info, " +
//            "(SELECT GROUP_CONCAT(DISTINCT m.name SEPARATOR ', ') " +
//            " FROM taskmaterialrelation tmr " +
//            " LEFT JOIN materials m ON tmr.material_id = m.id " +
//            " WHERE tmr.task_id = t.id) as materials_tools " +
//            "FROM agriculturaltask t " +
//            "LEFT JOIN taskassignment ta ON t.id = ta.task_id " +
//            "LEFT JOIN user u ON ta.user_id = u.user_id " +
//            "<where>" +
//            "<if test='queryParams != null'>" +
//            "<if test='queryParams.name != null and queryParams.name.trim() != \"\"'> AND t.task_name LIKE CONCAT('%', #{queryParams.name}, '%')</if>" +
//            "<if test='queryParams.assignee != null and queryParams.assignee.trim() != \"\"'> AND u.username LIKE CONCAT('%', #{queryParams.assignee}, '%')</if>" +
//            "<if test='queryParams.orchardInfo != null and queryParams.orchardInfo.trim() != \"\"'> AND EXISTS (" +
//            "   SELECT 1 FROM taskorchardrelation tor LEFT JOIN orchard_info o ON tor.orchard_id = o.id " +
//            "   WHERE tor.task_id = t.id AND o.name LIKE CONCAT('%', #{queryParams.orchardInfo}, '%')" +
//            ") </if>" +
//            "<if test='queryParams.createdAt != null and queryParams.createdAt.trim() != \"\"'> AND DATE(t.created_at) = DATE(#{queryParams.createdAt})</if>" +
//            "<if test='queryParams.status != null and queryParams.status.trim() != \"\"'> AND t.status = #{queryParams.status}</if>" +
//            "</if>" +
//            "</where> " +
//            "GROUP BY " +
//            "t.id, t.task_name, t.description, t.created_at, t.due_date, t.status, u.username " +
//            "</script>")
//    List<Map<String, Object>> selectTasksWithRelations(@Param("queryParams") Map<String, String> queryParams);


//
//    @Select("<script>" +
//            "SELECT " +
//            "t.id, " +
//            "t.task_name as name, " +
//            "t.description, " +
//            "t.created_at, " +
//            "t.due_date as end_date, " +
//            "o.name as orchard_info, " +
//            "GROUP_CONCAT(DISTINCT m.name SEPARATOR ', ') as materials_tools, " +
//            "t.status, " +
//            "u.username as assignee " +
//            "FROM agriculturaltask t " +
//            "LEFT JOIN taskassignment ta ON t.id = ta.task_id " +
//            "LEFT JOIN user u ON ta.user_id = u.user_id " +
//            "LEFT JOIN taskorchardrelation tor ON t.id = tor.task_id " +
//            "LEFT JOIN orchard_info o ON tor.orchard_id = o.id " +
//            "LEFT JOIN taskmaterialrelation tmr ON t.id = tmr.task_id " +
//            "LEFT JOIN materials m ON tmr.material_id = m.id " +
//            "<where>" +
//            "<if test='queryParams != null'>" +
//            "<foreach item='entry' index='key' collection='queryParams' open=' AND (' separator=' AND ' close=')'>" +
//            "${entry.key} #{entry.value}" +
//            "</foreach>" +
//            "</if>" +
//            "</where> " +
//            "GROUP BY " +
//            "t.id, t.task_name, t.description, t.created_at, t.due_date, t.status, u.username " +
//            "</script>")
//    List<Map<String, Object>> selectTasksWithRelations(Map<String, String> queryParams);
}